package thomas.javase.jooq;


import org.jooq.lambda.Seq;
import org.junit.Before;
import org.junit.Test;
import org.springframework.beans.BeanUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * https://dzone.com/articles/common-sql-clauses-and-their-equivalents-in-java-8-1
 */
public class JavaJooqSqlTest {

    private List<Teacher> teachers;

    private List<SimpleStudent>  students1;

    @Before
    public void assumeData(){
        teachers = new ArrayList<Teacher>();
        teachers.add(new Teacher(1, "Thomas", "Computer"));
        teachers.add(new Teacher(2, "Lynn", "Chinese"));

        students1 = new ArrayList<SimpleStudent>();
        students1.add(new SimpleStudent(1, "student1", 1, "Computer"));
        students1.add(new SimpleStudent(2, "student2", 2, "Chinese"));
        students1.add(new SimpleStudent(3, "student3", 1, "Computer"));
        students1.add(new SimpleStudent(4, "student4", 2, "Chinese"));
    }

    @Test
    public void useJavaJooqToQueryAsSql(){
        //get the students whose teacher is "Thomas"
        System.out.println("==================================  inner join  =================================================");
        Seq.seq(students1).innerJoin(
                Seq.seq(teachers).filter(t -> "Thomas".equals(t.getName())),
                (s, t) -> s.getTeacherId()==t.getId())
                .forEach(System.out::println);
        System.out.println("======================================  peek =============================================");
                Seq.seq(students1).innerJoin(
                        Seq.seq(teachers).filter(t -> "Thomas".equals(t.getName())),
                        (s, t) -> s.getTeacherId()==t.getId())
                        .peek(tuple -> BeanUtils.copyProperties(tuple.v2, tuple.v1)).forEach(System.out::println);
        System.out.println("======================================  unzip  =============================================");
        Seq.unzip(
                Seq.seq(students1).innerJoin(
                    Seq.seq(teachers).filter(t -> "Thomas".equals(t.getName())),
                     (s, t) -> s.getTeacherId()==t.getId())
                .peek(tuple -> BeanUtils.copyProperties(tuple.v2, tuple.v1)))
                .forEach(System.out::println);
        System.out.println("======================================  inner join final =============================================");
        Seq<SimpleStudent> students = Seq.unzip(
                Seq.seq(students1).innerJoin(
                        Seq.seq(teachers).filter(t -> "Thomas".equals(t.getName())),
                        (s, t) -> s.getTeacherId()==t.getId())
                        .peek(tuple -> BeanUtils.copyProperties(tuple.v2, tuple.v1)))
                .v1;
        students.forEach(System.out::println);


    }

    @Test
    public void useJavaJooqToQueryAsSqlInnerJoin(){
        System.out.println("======================================  inner join final  =============================================");
        Seq<SimpleStudent> students2 = Seq.unzip(
                Seq.seq(students1).innerJoin(
                        Seq.seq(teachers).filter(t -> "Thomas".equals(t.getName())),
                        (s, t) -> s.getTeacherId()==t.getId())
                        .peek(tuple -> {
                            tuple.v1.setTeacherName(tuple.v2.getName());
                        }))
                .v1;
        students2.forEach(System.out::println);
    }

    @Test
    public void leftOuterJoin(){
        System.out.println("======================================  left join final  =============================================");
        Seq<SimpleStudent> students2 = Seq.unzip(
                Seq.seq(students1).leftOuterJoin(
                        Seq.seq(teachers).filter(t -> "Thomas".equals(t.getName())),
                        (s, t) -> s.getTeacherId()==t.getId())
                        .peek(tuple -> {
                            if(tuple.v2 != null) {
                                tuple.v1.setTeacherName(tuple.v2.getName());
                            }
                        }))
                .v1;
        students2.forEach(System.out::println);
    }
}
